﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using eSYSU.DAL;
using eSYSU.Model;
using eSYSU.IBusiness;


namespace eSYSU.Business
{
    /// <summary>
    /// 新闻内容的操作类
    /// </summary>
    public class NewsService// : INews
    {

        #region INews成员


        /// <summary>
        /// 获取最新的N条新闻
        /// </summary>
        /// <param name="newsCount">要获取的新闻数量</param>
        /// <returns>已填充新闻内容的IList对象</returns>
        public static IList<News> GetLatestNews(int newsCount)
        {
            IList<News> list = new List<News>();
            IDictionary<string, object> parmsDic = new Dictionary<string, object>();
            parmsDic.Add("@NewsCount",newsCount);

            DataTable dt = SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.StoredProcedure, "proc_News_Get_Latest", parmsDic);
            
            foreach (DataRow r in dt.Rows)
            {
                News news = new News();

                news.id = Convert.ToInt32(r["NewsID"]);
                news.collegeid = Convert.ToInt32(r["CollegeID"]);
                news.title = Convert.ToString(r["Title"]);
                news.posttime = Convert.ToDateTime(r["PostTime"].ToString() == "" ? DateTime.Now : r["PostTime"]);
                news.embodytime = Convert.ToDateTime(r["EmbodyTime"].ToString() == "" ? DateTime.Now : r["EmbodyTime"]);
                news.origin = Convert.ToString(r["Origin"]);
                news.originalurl = Convert.ToString(r["OriginalUrl"]);
                news.keyword = Convert.ToString(r["Keyword"]);
                news.brief = Convert.ToString(r["Brief"]);
                news.content = Convert.ToString(r["Content"]);

                list.Add(news);
            }

            return list;
        }

        /// <summary>
        /// 获取最新的N条新闻
        /// </summary>
        /// <param name="newsCount">要获取的新闻数量</param>
        /// <returns>已填充新闻内容的DataTable对象</returns>
        public static DataTable GetLatestNewsTable(int newsCount)
        {
            DataTable dt = new DataTable();
            IList<News> list = new List<News>();
            list = GetLatestNews(newsCount);

            DataColumn NewsID = new DataColumn("新闻ID", typeof(Int32));
            DataColumn CollegeID = new DataColumn("分类编号", typeof(Int32));
            DataColumn Title = new DataColumn("新闻标题", typeof(string));
            DataColumn Author = new DataColumn("作者", typeof(string));
            DataColumn PostTime = new DataColumn("发布时间", typeof(DateTime));
            DataColumn EmbodyTime = new DataColumn("收录时间", typeof(DateTime));
            DataColumn Origin = new DataColumn("新闻来源", typeof(string));
            DataColumn OriginalUrl = new DataColumn("原文链接", typeof(string));
            DataColumn Keyword = new DataColumn("关键字", typeof(string));
            DataColumn Brief = new DataColumn("摘要", typeof(string));
            DataColumn Content = new DataColumn("内容", typeof(string));
            dt.Columns.AddRange(new DataColumn[] { NewsID, CollegeID, Title, Author, PostTime, EmbodyTime, Origin, OriginalUrl, Keyword, Brief, Content });

            foreach (News t in list)
            {
                DataRow newRow = dt.NewRow();
                newRow["新闻ID"] = t.id;
                newRow["分类编号"] = t.collegeid;
                newRow["新闻标题"] = t.title;
                newRow["作者"] = t.author;
                newRow["发布时间"] = t.posttime;
                newRow["收录时间"] = t.embodytime;
                newRow["新闻来源"] = t.origin;
                newRow["原文链接"] = t.originalurl;
                newRow["关键字"] = t.keyword;
                newRow["摘要"] = t.brief;
                newRow["内容"] = t.content;
                dt.Rows.Add(newRow);
            }

            return dt;
        }


        /// <summary>
        /// 根据指定数量的新闻综合内容，包含与新闻相关的所有信息
        /// </summary>
        /// <param name="newsCount">需获取的新闻数量</param>
        /// <returns>已填充新闻内容的DataTable对象</returns>
        public static DataTable GetLatestComplexNews(int newsCount)
        {
            IDictionary<string, object> parmsDic = new Dictionary<string, object>();
            parmsDic.Add("@NewsCount", newsCount);

            return SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.StoredProcedure, "proc_News_Get_Latest_v", parmsDic);
        }


        /// <summary>
        /// 根据指定数量的新闻简要内容（ID，标题，分类，发布时间）
        /// </summary>
        /// <param name="newsCount">需获取的新闻数量</param>
        /// <returns>已填充新闻内容的DataTable对象</returns>
        public static DataTable GetLatestNewsBrief(int newsCount)
        {
            IDictionary<string, object> parmsDic = new Dictionary<string, object>();
            parmsDic.Add("@NewsCount", newsCount);

            return SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.StoredProcedure, "proc_News_Get_Latest_Brief", parmsDic);
        }


        /// <summary>
        /// 根据指定数量的热点新闻简要内容（ID，标题，分类，发布时间）
        /// </summary>
        /// <param name="newsCount">需获取的新闻数量</param>
        /// <returns>已填充新闻内容的DataTable对象</returns>
        public static DataTable GetLatestHotNewsBrief(int newsCount)
        {
            string sql = String.Format("select newsid,title,shortname,posttime from news, college as c where news.collegeid = c.id order by Hits limit 0,{0}", newsCount);
            return SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.Text, sql, null);
        }


        /// <summary>
        /// 根据指定ID的新闻综合内容，包含与新闻相关的所有信息
        /// </summary>
        /// <param name="newsID">新闻ID</param>
        /// <returns>已填充新闻内容的DataTable对象</returns>
        public static DataTable GetComplexNewsByID(int newsID)
        {
            IDictionary<string, object> parmsDic = new Dictionary<string, object>();
            parmsDic.Add("@NewsID", newsID);

            return SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.StoredProcedure, "proc_News_Get_By_ID_v", parmsDic);
        }


        /// <summary>
        /// 根据ID得到新闻内容
        /// </summary>
        /// <param name="newsID">ID</param>
        /// <returns>新闻</returns>
        public static News GetNewsById(int newsID)
        {
            IDictionary<string, object> parmsDic = new Dictionary<string, object>();
            parmsDic.Add("@NewsID", newsID);

            using (IDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.StoredProcedure, "proc_News_Get_By_ID", parmsDic))
            {
                if (rdr.Read())
                {
                    News news = new News();

                    news.id = Convert.ToInt32(rdr["NewsID"]);
                    news.collegeid = Convert.ToInt32(rdr["CollegeID"]);
                    news.title = Convert.ToString(rdr["Title"]);
                    news.author = Convert.ToString(rdr["Author"]);
                    news.posttime = Convert.ToDateTime(rdr["PostTime"].ToString() == "" ? DateTime.Now : rdr["PostTime"]);
                    news.embodytime = Convert.ToDateTime(rdr["EmbodyTime"].ToString() == "" ? DateTime.Now : rdr["EmbodyTime"]);
                    news.origin = Convert.ToString(rdr["Origin"]);
                    news.originalurl = Convert.ToString(rdr["OriginalUrl"]);
                    news.keyword = Convert.ToString(rdr["Keyword"]);
                    news.brief = Convert.ToString(rdr["Brief"]);
                    news.content = Convert.ToString(rdr["Content"]);

                    return news;
                }
                else
                {
                    return null;
                }
            }
        }


        /// <summary>
        /// 获取用户订阅的新闻列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="startIndex">新闻列表起始索引</param>
        /// <param name="newsCount">新闻数量</param>
        /// <returns>新闻列表DataTable对象</returns>
        public static DataTable GetUserSubsciptionNews(string userID, int startIndex, int newsCount)
        {
            if (startIndex < 0 || newsCount < 1)  //起始索引不能小于零，且新闻数量不能小于1
                return null;

            string sqlstr = "select categoryList from subscription where userId = 1";
            DataTable dt = eSYSU.DAL.SqlHelper.GetDataTable(sqlstr);
            if (dt.Rows.Count > 0)
            {
                string college = dt.Rows[0][0].ToString().Trim().Replace(' ', ',');
                if (!college.Equals("0"))     //用户订阅了所有新闻分类
                {
                    sqlstr = string.Format("Select NewsID,title,College,Author,PostTime,OriginalURL From v_News where College in (select Name from College where Id in({0})) Order By NewsID DESC limit {1},{2}", college, startIndex, newsCount);
                }
            }
            else
            {
                sqlstr = string.Format("Select NewsID,title,College,Author,PostTime,OriginalURL From v_News Order By NewsID DESC limit {0},{1}", startIndex, newsCount);
            }
            return SqlHelper.GetDataTable(sqlstr);
        }

        #endregion
    }
}